package com.hongdee.crm.statistics.task.service.impl;

import com.hongdee.annotation.MethodComment;
import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.Constant;
import com.hongdee.crm.statistics.task.dao.ITCrmCarCountTaskDao;
import com.hongdee.crm.statistics.task.service.ITCrmCarCountTaskService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.DecimalFormat;
import java.util.*;

/**
 * Created by My on 2017/5/24.
 */
@Service
public class TCrmCountTaskServiceImpl implements ITCrmCarCountTaskService {

    @Autowired
    private ITCrmCarCountTaskDao crmCarCountTaskDao;

    private String[] MONTHS = {"", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", "eleven", "twelve"};

    //-----------------------------------事故车辆回厂保养率-----------------------------------

    /**
     * 事故车回厂保养到店率
     * 1、找出所有的公司信息
     * 2、当前月如当前为5月，找出4月、3月、2月的信息
     * 3、循环更新 5 4 3 2 月的数据
     * 4、插入或更新事故车到店率信息
     *
     * @return
     */
    @Override
    @MethodComment(name = "事故车辆回厂保养率")
    public Integer accidentCarBackToFacQuartz() throws Exception {
        Integer nums = 0;
        //1、查询出所有的公司信息
        List<Map<String, Object>> companyList = this.crmCarCountTaskDao.queryCompanyByWorkOrder("t_crm_car_workorder");
        //遍历公司信息
        for (Map<String, Object> companyMap : companyList) {

            String companyId = StringUtil.formatNull(companyMap.get("company_id"));
            //获得当前月向前推进三个月的时间 包含当前月
            List<String> monthList = this.getNowAndBeforThreeMonth(companyId, "T_CRM_KPI_SG");
            //遍历月份开始计算
            for (String monthTime : monthList) {
                nums += this.analyCompanyToaccidentCar(monthTime, companyId);
            }
        }
        return nums;
    }

    @Override
    public Integer analyCompanyToaccidentCar(String monthTime, String companyId) throws Exception {
        Integer nums = 0;
        //当前月
        //2017年1月评测
        //2016年1月的事故车 400，
        //16年1月的事故车在16年2月1日开始至2017年2月1日之前的进厂车辆数
        //将时间格式化为月初时间
        Long nowMonth = DateUtils.monthSToLongTime(monthTime);
        //去年一月开始
        Long beforYearStartDate = DateUtils.addMonthLongTime(nowMonth, -12);
        Long beforYearSecondDate = DateUtils.addMonthLongTime(nowMonth, -11);
        Long nowNextMonthDate = DateUtils.addMonthLongTime(nowMonth, +1);
        String year=DateUtils.formatLongTime(beforYearStartDate,"YYYY");
        String sql = "select COUNT(DISTINCT vin) count,'事故车数' label,'"+year+"' as base_year from t_crm_car_workorder  where balance_date>=? and balance_date<? and company_id=? and real_amount>'0.00' and wx_type='事故车'";
        //事故车辆数
        Map<String, Object> carInfo = this.crmCarCountTaskDao.info(sql, beforYearStartDate, beforYearSecondDate, companyId);
        nums += this.saveKPISG(carInfo, nowMonth, companyId);

        StringBuffer sqlStr = new StringBuffer();
        List<Object> params = new ArrayList<>();
        sqlStr.append(" select COUNT(DISTINCT vin) count,'到店数' label from t_crm_car_workorder w where real_amount>'0.00' and  balance_date>=? ");
        params.add(beforYearStartDate);
        sqlStr.append(" and balance_date<? ");
        params.add(beforYearSecondDate);
        sqlStr.append(" and company_id=? ");
        params.add(companyId);
        sqlStr.append(" and wx_type='事故车' and EXISTS(select * from t_crm_car_workorder where ");
        sqlStr.append(" balance_date>=? ");
        params.add(beforYearSecondDate);
        sqlStr.append(" and balance_date<? ");
        params.add(nowNextMonthDate);
        sqlStr.append(" and company_id=? ");
        params.add(companyId);
        sqlStr.append(" and wx_type in ('首保','二保','定保') and real_amount>'0.00'  and w.vin=vin group by vin ) ");
        Map<String, Object> carCountInfo = this.crmCarCountTaskDao.info(sqlStr.toString(), params.toArray());
        //保养车辆数
        nums += this.saveKPISG(carCountInfo, nowMonth, companyId);
        Integer cars = Integer.valueOf(String.valueOf(carInfo.get("count")));
        Integer carCounts = Integer.valueOf(String.valueOf(carCountInfo.get("count")));
        Map<String, Object> ratingMap = new HashMap<>();
        ratingMap.put("label", "到店率");
        //判断进厂是否为0
        if (cars == 0) {
            ratingMap.put("count", "0.0%");
        } else {
            Float carsF = Float.valueOf(cars);
            Float num = (Float.valueOf(carCounts) / carsF) * 100;
            DecimalFormat decimalFormat = new DecimalFormat(".00");//构造方法的字符格式这里如果小数不足2位,会以0补足.
            String p = decimalFormat.format(num);
            ratingMap.put("count", p + "%");
        }
        nums += this.saveKPISG(ratingMap, nowMonth, companyId);
        return nums;
    }

    /**
     * 判断是新增还是修改
     *
     * @param infoMap
     * @param nowTime
     * @param companyId
     * @throws Exception
     */
    private Integer saveKPISG(Map<String, Object> infoMap, Long nowTime, String companyId) throws Exception {
        String label = StringUtil.formatNull(infoMap.get("label"));
        String count = String.valueOf(infoMap.get("count"));
        String year = DateUtils.getTimeStr(new Date(nowTime), "yyyy");
        String month = DateUtils.getTimeStr(new Date(nowTime), "MM");
        Map<String, Object> kpiSgInfo = crmCarCountTaskDao.queryAccidentCarInfo(companyId, year, label);
        StringBuffer sql = new StringBuffer();
        String monthStr = MONTHS[Integer.valueOf(month)];
        String baseYear = "";
        if(!"".equals(StringUtil.formatEmpty(infoMap.get("baseYear")))){
            baseYear=StringUtil.formatEmpty(infoMap.get("baseYear"));
        }
        if (kpiSgInfo.size() == 0) {
            //新增
            sql.append("insert into T_CRM_KPI_SG (id,company_id,count_tag,count_year," + monthStr + ",base_year) values" +
                    "(replace(UUID(),'-',''),'" + companyId + "','" + label + "','" + year + "','" + count + "','"+baseYear+"')");
        } else {
            String id = StringUtil.formatNull(kpiSgInfo.get("id"));
            //更新
            sql.append("update T_CRM_KPI_SG set " + monthStr + "='" + count + "' where id='" + id + "'");
        }
        return this.crmCarCountTaskDao.executeSQL(sql.toString());
    }

    /**
     * 当前月向前推3个月
     *
     * @return
     * @throws Exception
     */
    public List<String> getNowAndBeforThreeMonth(String companyId, String tabName) throws Exception {
        List<String> times = new ArrayList<>();
        //判断该公司下是否已经有生成的数据 如果没有生成12个月的，如果有 则生成3个月
        boolean flag = this.crmCarCountTaskDao.queryStatisticsDataByCompany(companyId, tabName);
        // 向前生成3个月的数据
        int preCount = 12;
//        if (flag) {
//            preCount = 3;
//        }
        // 向后生成12个月的数据
        int newxCount = 12;

        //向前生成
        for (int i = preCount; i >= 0; i--) {
            Long time = DateUtils.addMonthLongTime(new Date().getTime(), -i);
            String timeStr = DateUtils.formatLongTime(time);
            times.add(timeStr);
        }
        //向后生成
        for (int i = 1; i < preCount; i++) {
            Long time = DateUtils.addMonthLongTime(new Date().getTime(), i);
            String timeStr = DateUtils.formatLongTime(time);
            times.add(timeStr);
        }
        return times;
    }

    @Override
    public Map<String,Object> getNowAndBeforThreeMonthMap(String companyId, String tabName) throws Exception {
        Map<String,Object> map=new HashMap<>();
        List<String> times = new ArrayList<>();
        Set<String> yearSet=new HashSet<>();
        //判断该公司下是否已经有生成的数据 如果没有生成12个月的，如果有 则生成3个月
        boolean flag = this.crmCarCountTaskDao.queryStatisticsDataByCompany(companyId, tabName);
        // 向前生成3个月的数据
        int preCount = 12;
//        if (flag) {
//            preCount = 3;
//        }
        // 向后生成12个月的数据
        int newxCount = 12;

        //向前生成
        for (int i = preCount; i >= 0; i--) {
            Long time = DateUtils.addMonthLongTime(new Date().getTime(), -i);
            String year=DateUtils.formatLongTime(time,"yyyy");
            yearSet.add(year);
            String timeStr = DateUtils.formatLongTime(time);
            times.add(timeStr);
        }
        //向后生成
        for (int i = 1; i < preCount; i++) {
            Long time = DateUtils.addMonthLongTime(new Date().getTime(), i);
            String year=DateUtils.formatLongTime(time,"yyyy");
            yearSet.add(year);
            String timeStr = DateUtils.formatLongTime(time);
            times.add(timeStr);
        }
        map.put("years",yearSet);
        map.put("times",times);
        return map;
    }


    /**
     * 事故车数据穿透明细
     * @param month
     * @param year
     * @param uid
     * @param type
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map<String,Object> sgCarPree(String companyId,String month,String year,String uid,String type,String start,String limit) throws Exception {
        //获得月份
        month=this.getMonthIndex(month);
        if("".equals(month)){
            throw new Exception("月份选择错误!!");
        }
        String date=year+"-"+month+"-01";
        Long nowMonth = DateUtils.monthSToLongTime(date);

        //去年一月开始
        Long beforYearStartDate = DateUtils.addMonthLongTime(nowMonth, -12);
        Long beforYearSecondDate = DateUtils.addMonthLongTime(nowMonth, -11);
        Long nowNextMonthDate = DateUtils.addMonthLongTime(nowMonth, +1);
        StringBuffer sql=new StringBuffer();
        List<Object> params=new ArrayList<>();
        sql.append(" select * from ( ");
        if("car".equals(type)){
            this.sgCarPreeListToSql(sql,params,beforYearStartDate,beforYearSecondDate,companyId);
        }
        if("back".equals(type)){
            this.sgCarBackPreeListToSql(sql,params,beforYearStartDate,beforYearSecondDate,companyId,nowNextMonthDate);
        }
        sql.append(" )temp ");
        return this.crmCarCountTaskDao.page(sql.toString(), params.toArray(),start,limit);
    }

    /**
     * 事故车车辆数
     * @param sql
     * @param params
     * @param beforYearStartDate
     * @param beforYearSecondDate
     * @param companyId
     */
    private void sgCarPreeListToSql(StringBuffer sql,List<Object> params,Long beforYearStartDate,Long beforYearSecondDate,
                                    String companyId){
        sql.append("SELECT worder.plate_num,worder.vin,car.owner_name,car.owner_tel,car.brand,car.series,car.model,  car.car_age,max(worder.run_mile) runMile,FROM_UNIXTIME(MAX(worder.balance_date) / 1000) balanceDate  FROM    t_crm_car_workorder worder    LEFT JOIN t_crm_car car ON worder.vin = car.vin AND worder.company_id = car.company_id   where worder.balance_date>=? and worder.balance_date<? and worder.company_id=? and worder.real_amount>'0.00' and worder.wx_type='事故车'   group by worder.vin ");
        params.add(beforYearStartDate);
        params.add(beforYearSecondDate);
        params.add(companyId);
    }

    /**
     * 事故车回店数
     * @param sqlStr
     * @param params
     * @param beforYearStartDate
     * @param beforYearSecondDate
     * @param companyId
     * @param nowNextMonthDate
     */
    private void sgCarBackPreeListToSql(StringBuffer sqlStr,List<Object> params,Long beforYearStartDate,Long beforYearSecondDate,
                                        String companyId,Long nowNextMonthDate){
        sqlStr.append("SELECT worder.plate_num,worder.vin,car.owner_name,car.owner_tel,car.brand,car.series,car.model," +
                        " car.car_age,max(worder.run_mile) runMile,FROM_UNIXTIME(MAX(worder.balance_date) / 1000) balanceDate" +
                        " FROM  " +
                        " t_crm_car_workorder worder  " +
                        " LEFT JOIN t_crm_car car ON worder.vin = car.vin AND worder.company_id = car.company_id " +
                        " where worder.balance_date>=? and worder.real_amount>'0.00' ");
        params.add(beforYearStartDate);
        sqlStr.append(" and worder.balance_date<? ");
        params.add(beforYearSecondDate);
        sqlStr.append(" and worder.company_id=? ");
        params.add(companyId);
        sqlStr.append(" and worder.wx_type='事故车' and EXISTS(select * from t_crm_car_workorder where ");
        sqlStr.append(" balance_date>=? ");
        params.add(beforYearSecondDate);
        sqlStr.append(" and balance_date<? ");
        params.add(nowNextMonthDate);
        sqlStr.append(" and company_id=? ");
        params.add(companyId);
        sqlStr.append(" and wx_type in ('首保','二保','定保') and real_amount>'0.00'  and worder.vin=vin group by vin ) group by worder.vin");
    }

    //-----------------------------------质保车辆回厂保养率-----------------------------------

    /**
     * 质保到期回店率
     * 1、找出拥有有车辆的公司
     * 2、
     *
     * @return
     * @throws Exception
     */
    @Override
    @MethodComment(name = "质保到期保养回厂率")
    public Integer warrantyCarBackToFacQuartz() throws Exception {
//        this.crmCarCountTaskDao.executeSQL("delete from t_crm_kpi_zb ");
//        int nums = 0;
//        //1、查询出所有的公司信息
//        List<Map<String, Object>> companyList = this.crmCarCountTaskDao.queryCompanyByWorkOrder("t_crm_car_workorder");
//        //遍历公司信息
//        for (Map<String, Object> companyMap : companyList) {
//            String companyId = StringUtil.formatNull(companyMap.get("company_id"));
//            //获得当前月向前推进三个月的时间 包含当前月
//            List<String> monthList = this.getNowAndBeforThreeMonth(companyId, "t_crm_kpi_zb");
//            //遍历月份开始计算
//            for (String monthTime : monthList) {
//                nums += this.analyCompanyToWarrantyCar(monthTime,companyId) ;
//            }
//        }
//        return nums;

//        this.crmCarCountTaskDao.executeSQL("delete from t_crm_kpi_zb ");
        int nums = 0;
        //1、查询出所有的公司信息
        List<Map<String, Object>> companyList = this.crmCarCountTaskDao.queryCompanyByWorkOrder("t_crm_kpi_zb");
        //遍历公司信息
        for (Map<String, Object> companyMap : companyList) {
            String companyId = StringUtil.formatNull(companyMap.get("company_id"));
            //获得当前月向前推进三个月的时间 包含当前月
            //1、清除本公司下的出保kpi表
            this.crmCarCountTaskDao.executeSQL("delete from t_crm_kpi_zb where company_id=?",companyId);
            Map<String,Object> map= this.getNowAndBeforThreeMonthMap(companyId, "t_crm_kpi_zb");
            //插入标签及年
            saveCountYearInfo(map,companyId,"t_crm_kpi_zb");
            List<String> monthList=(List<String>)map.get("times");
            //遍历月份开始计算
            for (String monthTime : monthList) {
                this.analyCompanyToWarrantyCarToSql(monthTime,companyId) ;
            }
        }
        return nums;
    }

    private void saveCountYearInfo(Map<String,Object> map,String companyId,String tableName) throws Exception {
        String[] countTags={"车辆数","回店数","到店率"};
        Set<String> years = (Set<String>) map.get("years");
        for(String countTag:countTags) {
            for (String year : years) {
                //3、插入每一年的信息标签插入到出保kpi表
                String sql="insert into "+tableName+" (id,sa_id,sa,count_tag,count_year,company_id)  " +
                        " SELECT UUID() as id, detail.u_id,cuser.u_name, '"+countTag+"' count_tag, " +
                        " '" + year + "' count_year,'"+companyId+"' company_id " +
                        " FROM    t_crm_car_conf_detail detail,    t_bc_user cuser   WHERE    detail.u_id = cuser.id   AND " +
                        " detail.company_id =?   AND detail.role_id = ?  group by u_id  " +
                        " UNION" +
                        " select UUID() as id,'未分配' u_id,'未分配' u_name,'"+countTag+"' count_tag," +
                        " '" + year + "' count_year,'"+companyId+"' company_id ";
                this.crmCarCountTaskDao.executeSQL(sql, companyId, Constant.SA_ROLE_ID);
            }
        }
    }


    public void analyCompanyToWarrantyCarToSql(String monthTime, String companyId) throws Exception {
        //2、获得每一年
        Long nowMonth = DateUtils.monthSToLongTime(monthTime);
        String countYear = DateUtils.getTimeStr(new Date(nowMonth), "yyyy");
        String month = DateUtils.getTimeStr(new Date(nowMonth), "MM");
        String monthStr = MONTHS[Integer.valueOf(month)];
        Integer nums = 0;
        //当前月
        //2017年1月评测
        //统计2017年2月份到期的车辆数
        //计算2月份到期的车辆，在到期的前两个月回厂保养率
        //将时间格式化为月初时间
        //向前推35个月，由于车辆没有质保结束时间 以购买时间为准
        Long before35MonthTime = DateUtils.addMonthLongTime(nowMonth, -35);
        System.out.println(countYear+"--------"+DateUtils.formatLongTime(nowMonth,"yyyy-MM-dd")+"----"+DateUtils.formatLongTime(before35MonthTime,"yyyy-MM-dd"));
        String baseYear=DateUtils.formatLongTime(before35MonthTime,"YYYY");
        //向前推34个月，由于车辆没有质保结束时间 以购买时间为准
        Long before34MonthTime = DateUtils.addMonthLongTime(nowMonth, -34);
        this.warrantyCarSave(monthStr,baseYear,countYear,companyId,before35MonthTime,before34MonthTime);
        this.warrantyCarBackFacSave(monthStr,countYear,companyId,before35MonthTime,before34MonthTime,nowMonth);
        this.warranyCarBackRatingSave(monthStr,companyId,countYear,"t_crm_kpi_zb");
        this.updBaseYear(countYear,companyId,"t_crm_kpi_zb");
    }

    /**
     * 计算车辆数
     * @param monthStr
     * @param baseYear
     * @param countYear
     * @param companyId
     * @param before35MonthTime
     * @param before34MonthTime
     * @throws Exception
     */
    private void warrantyCarSave(String monthStr,String baseYear,String countYear,String companyId,Long before35MonthTime,
                                 Long before34MonthTime) throws Exception {
        StringBuffer sql=new StringBuffer();
        sql.append("UPDATE t_crm_kpi_zb zb, ( " +
                " SELECT COUNT(DISTINCT car.vin) "+monthStr+", " +
                " CASE WHEN detail.u_id IS NULL THEN '未分配' " +
                "      WHEN detail.u_id IS NOT NULL THEN detail.u_id " +
                " END AS uid, " +
                " CASE WHEN detail.u_name IS NULL AND detail.u_id IS NULL THEN '未分配' " +
                "      WHEN detail.u_name IS NOT NULL THEN detail.u_name " +
                "      WHEN detail.u_name IS NULL AND detail.u_id IS NOT NULL THEN RIGHT (detail.u_id, 8) " +
                " END AS uname, " +
                " '车辆数' label,car.company_id,'"+baseYear+"' base_year,'"+countYear+"' count_year" +
                " FROM t_crm_car car " +
                " LEFT JOIN ( " +
                " SELECT  detail.vin,detail.company_id,detail.u_id,cuser.u_name " +
                " FROM t_crm_car_conf_detail detail,t_bc_user cuser " +
                " WHERE detail.u_id = cuser.id AND detail.company_id = ? AND detail.role_id = ? " +
                " ) detail ON car.vin = detail.vin AND car.company_id = detail.company_id " +
                " WHERE car.buy_date >= ? AND car.buy_date < ? AND car.company_id = ? " +
                " GROUP BY  detail.u_id " +
                " ) t " +
                " SET zb."+monthStr+" = t."+monthStr+", zb.base_year =if(zb.base_year is null,t.base_year,zb.base_year) " +
                " WHERE zb.company_id = t.company_id AND zb.sa_id = t.uid AND zb.sa = t.uname " +
                " AND zb.count_tag = t.label AND zb.count_year = t.count_year");
        this.crmCarCountTaskDao.executeSQL(sql.toString(),companyId,Constant.SA_ROLE_ID,before35MonthTime,before34MonthTime,companyId);
    }

    /**
     * 计算回店数信息
     * @param monthStr
     * @param countYear
     * @param companyId
     * @param before35MonthTime
     * @param before34MonthTime
     * @param nowMonth
     * @throws Exception
     */
    private void warrantyCarBackFacSave(String monthStr,String countYear,String companyId,Long before35MonthTime,
                                        Long before34MonthTime,Long nowMonth) throws Exception {
        Long beforOneMonth = DateUtils.addMonthLongTime(nowMonth, -1);
        Long nextOneMonth = DateUtils.addMonthLongTime(nowMonth, +1);
        StringBuffer sql=new StringBuffer();
        List<Object> params=new ArrayList<>();
        sql.append("update t_crm_kpi_zb zb,( " +
                " SELECT COUNT(DISTINCT worder.vin) "+monthStr+", " +
                " CASE WHEN car.u_id IS NULL THEN '未分配' " +
                "      WHEN car.u_id IS NOT NULL THEN car.u_id END AS uid, " +
                " CASE WHEN car.u_name IS NULL AND car.u_id IS NULL THEN '未分配' WHEN car.u_name IS NOT NULL THEN car.u_name " +
                "      WHEN car.u_name IS NULL AND car.u_id IS NOT NULL THEN RIGHT (car.u_id, 8) END AS uname, " +
                " '回店数' count_tag,car.company_id,'"+countYear+"' count_year " +
                " FROM( " +
                " SELECT * FROM t_crm_car_workorder " +
                " WHERE real_amount > '0.00' AND wx_type IN ('首保', '二保', '定保') " +
                " AND company_id =? AND balance_date >=? AND balance_date <? " );
        params.add(companyId);
        params.add(beforOneMonth);
        params.add(nextOneMonth);
        sql.append(" ) worder " +
                " LEFT JOIN ( " +
                " SELECT car.vin,detail.u_id,detail.u_name,detail.company_id " +
                " FROM t_crm_car car " +
                " LEFT JOIN ( " +
                " SELECT detail.vin,detail.company_id,detail.u_id,cuser.u_name " +
                " FROM t_crm_car_conf_detail detail,t_bc_user cuser " +
                " WHERE detail.u_id = cuser.id " +
                "  AND detail.company_id =? AND detail.role_id = ? " +
                " ) detail ON car.vin = detail.vin AND car.company_id = detail.company_id " +
                " WHERE car.buy_date >=? AND car.buy_date <? AND car.company_id =? " +
                " ) car ON worder.vin = car.vin and worder.company_id = car.company_id " +
                " WHERE car.vin IS NOT NULL GROUP BY car.u_id " +
                " ) t set zb."+monthStr+"=t."+monthStr+" " +
                " where zb.sa_id=t.uid and zb.sa=t.uname and zb.count_tag=t.count_tag and zb.count_year=t.count_year");
        params.add(companyId);
        params.add(Constant.SA_ROLE_ID);
        params.add(before35MonthTime);
        params.add(before34MonthTime);
        params.add(companyId);
        this.crmCarCountTaskDao.executeSQL(sql.toString(),params.toArray());
    }

    private void updBaseYear(String countYear,String companyId,String tableName) throws Exception {
        StringBuffer sql=new StringBuffer();
        sql.append("UPDATE  `"+tableName+"` zb, ( " +
                "  SELECT * FROM "+tableName+" " +
                "  WHERE company_id = ? " +
                "  AND count_tag = '车辆数' AND count_year = ? GROUP BY company_id ) temp1 " +
                " set zb.base_year=temp1.base_year " +
                " WHERE " +
                " zb.count_year=temp1.count_year " +
                " AND zb.company_id = ? " +
                " AND zb.count_tag = '车辆数' " +
                " AND zb.sa = '未分配' " +
                " AND zb.count_year=? " +
                " AND zb.base_year IS NULL");
        this.crmCarCountTaskDao.executeSQL(sql.toString(),companyId,countYear,companyId,countYear);
    }
    
    /**
     * 计算到店率
     * @param monthStr
     * @param companyId
     * @param countYear
     * @throws Exception
     */
    private void warranyCarBackRatingSave(String monthStr,String companyId,String countYear,String tableName) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<Object> params=new ArrayList<>();
        sql.append("update "+tableName+" zb,( " +
                " select car.company_id,car.sa,car.sa_id,car.count_year,car.cars,bac.fac," +
                " if(car.cars='0','0.00',ROUND((bac.fac / car.cars) * 100, 2)) lv from ( " +
                " select company_id,sa,sa_id,count_year,count_tag,CASE WHEN "+monthStr+" is null THEN '0' ELSE "+monthStr+" END cars from " +
                " "+tableName+" where count_tag='车辆数' and count_year=? and company_id=? " );
        params.add(countYear);
        params.add(companyId);
        sql.append(" ) car,( " +
                " select company_id,sa,sa_id,count_year,count_tag,CASE WHEN "+monthStr+" is null THEN '0' ELSE "+monthStr+" END fac from " +
                " "+tableName+" where count_tag='回店数' and count_year=? and company_id=? ");
        params.add(countYear);
        params.add(companyId);
        sql.append(" ) bac where car.count_year=bac.count_year and car.company_id=bac.company_id and car.sa=bac.sa and " +
                " car.sa_id=car.sa_id " +
                " ) temp " +
                " set zb."+monthStr+"=concat(temp.lv,'%') "+
                " where zb.count_tag='到店率' and zb.company_id=? and zb.company_id=temp.company_id and zb.count_year=temp.count_year " +
                " and zb.sa=temp.sa and zb.sa_id=temp.sa_id");
        params.add(companyId);
        this.crmCarCountTaskDao.executeSQL(sql.toString(),params.toArray());
    }
//    
//
//
//    /**
//     * 根据时间 生成质保回店率
//     *
//     * @param monthTime
//     * @param companyId
//     * @return
//     * @throws Exception
//     */
//    public Integer analyCompanyToWarrantyCar(String monthTime, String companyId) throws Exception {
//        Integer nums = 0;
//        //当前月
//        //2017年1月评测
//        //统计2017年2月份到期的车辆数
//        //计算2月份到期的车辆，在到期的前两个月回厂保养率
//        //将时间格式化为月初时间
//        Long nowMonth = DateUtils.monthSToLongTime(monthTime);
//        //向前推35个月，由于车辆没有质保结束时间 以购买时间为准
//        Long before35MonthTime = DateUtils.addMonthLongTime(nowMonth, -35);
//        //向前推34个月，由于车辆没有质保结束时间 以购买时间为准
//        Long before34MonthTime = DateUtils.addMonthLongTime(nowMonth, -34);
//        //统计车辆数
//        String year=DateUtils.formatLongTime(before35MonthTime,"YYYY");
//        String sqlCars = " select COUNT(DISTINCT car.vin) count,  CASE WHEN detail.u_id is null then '未分配' WHEN detail.u_id is not null THEN detail.u_id end as uid, CASE   WHEN detail.u_name is null and detail.u_id is NULL then '未分配'   WHEN detail.u_name is not null THEN detail.u_name   WHEN detail.u_name IS NULL and detail.u_id is NOT NULL THEN RIGHT(detail.u_id,8)   end as uname,  '车辆数' label,'"+year+"' as base_year   from t_crm_car car   left join  "+
//                " (select detail.vin,detail.company_id,detail.u_id,cuser.u_name from t_crm_car_conf_detail detail ,t_bc_user cuser where  detail.u_id = cuser.id   and detail.company_id=? and detail.role_id='"+ Constant.SA_ROLE_ID+"'   ) detail on car.vin=detail.vin and car.company_id=detail.company_id "+
//
//                " where car.buy_date>=? and car.buy_date<? and car.company_id=? group by detail.u_id";
//        List<Map<String, Object>> carList = this.crmCarCountTaskDao.list(sqlCars, companyId,before35MonthTime, before34MonthTime, companyId);
//        nums += saveKPIZBAndDb(carList, nowMonth, companyId, "t_crm_kpi_zb");
//        //统计回店数
//        List<Map<String, Object>> backList = this.getZbBackToFac(nowMonth, before35MonthTime, before34MonthTime, companyId);
//        nums += saveKPIZBAndDb(backList, nowMonth, companyId, "t_crm_kpi_zb");
//        nums += this.saveRatingZB(carList, backList, nowMonth, companyId, "t_crm_kpi_zb");
//        return nums;
//    }
//
//    /**
//     * 获得质保保养回店信息
//     * 计算当前月 及上个月的保养回店信息
//     *
//     * @param nowMonth
//     * @param before35MonthTime
//     * @param before34MonthTime
//     * @param companyId
//     * @return
//     * @throws Exception
//     */
//    private List<Map<String, Object>> getZbBackToFac(Long nowMonth, Long before35MonthTime, long before34MonthTime, String companyId) throws Exception {
//        StringBuffer sqlback = new StringBuffer();
//        List<Object> params = new ArrayList<>();
//        sqlback.append("select COUNT(DISTINCT worder.vin) count,  CASE WHEN car.u_id is null then '未分配' WHEN car.u_id is not null THEN car.u_id end as uid, CASE   WHEN car.u_name is null and car.u_id is NULL then '未分配'   WHEN car.u_name is not null THEN car.u_name   WHEN car.u_name IS NULL and car.u_id is NOT NULL THEN RIGHT(car.u_id,8)   end as uname, '回店数' label from (  " +
//                //增加产值大于0
//                "select * from t_crm_car_workorder where real_amount > '0.00' and  wx_type in('首保','二保','定保') and company_id=?");
//        params.add(companyId);
//        Long beforOneMonth = DateUtils.addMonthLongTime(nowMonth, -1);
//        Long nextOneMonth = DateUtils.addMonthLongTime(nowMonth, +1);
//        sqlback.append("and balance_date>=? and balance_date<? ");
//        params.add(beforOneMonth);
//        params.add(nextOneMonth);
//        sqlback.append(") worder   LEFT join (   select car.vin,detail.u_id,detail.u_name    from t_crm_car car   left join  "+
//                " (select detail.vin,detail.company_id,detail.u_id,cuser.u_name from t_crm_car_conf_detail detail ,t_bc_user cuser where  detail.u_id = cuser.id   and detail.company_id=? and detail.role_id='"+ Constant.SA_ROLE_ID+"'   ) detail on car.vin=detail.vin and car.company_id=detail.company_id "+
//                "where car.buy_date>=? and car.buy_date<? and car.company_id=? )car  ");
//        params.add(companyId);
//        params.add(before35MonthTime);
//        params.add(before34MonthTime);
//        params.add(companyId);
//        sqlback.append("ON    worder.vin=car.vin  where car.vin is not null  group by car.u_id  ");
//        List<Map<String, Object>> backList = this.crmCarCountTaskDao.list(sqlback.toString(), params.toArray());
//        return backList;
//    }
//
    /**
     * 保存质保保养回店率
     *
     * @param carList
     * @param backList
     * @param nowMonth
     * @param companyId
     * @return
     * @throws Exception
     */
    private Integer saveRatingZB(List<Map<String, Object>> carList, List<Map<String, Object>> backList, Long nowMonth, String companyId, String tabName) throws Exception {
        List<Map<String, Object>> ratingMapList = new ArrayList<>();
        for (Map<String, Object> carMap : carList) {
            Map<String, Object> raingMap = new HashMap<>();
            raingMap.put("label", "到店率");
            //获得标签
            String label = StringUtil.formatNull(carMap.get("label"));
            Float count = Float.valueOf(String.valueOf(carMap.get("count")));
            String uid = String.valueOf(carMap.get("uid"));
            String uname = String.valueOf(carMap.get("uname"));
            raingMap.put("uid", uid);
            Float f = 0f;
            //遍历回店的车辆信息 看是否有相同的人员信息上如果有则计算
            for (Map<String, Object> backMap : backList) {
                Float backCount = Float.valueOf(String.valueOf(backMap.get("count")));
                String backUid = String.valueOf(backMap.get("uid"));
                if (uid.equals(backUid)) {
                    f = backCount;
                    break;
                }
            }
            if (count == 0) {
                raingMap.put("count", "0.0%");
            } else {
                if (f > 0) {
                    Float num = (f / count) * 100;
                    DecimalFormat decimalFormat = new DecimalFormat(".00");//构造方法的字符格式这里如果小数不足2位,会以0补足.
                    String p = decimalFormat.format(num);
                    raingMap.put("count", p + "%");
                } else {
                    raingMap.put("count", "0.0%");
                }
            }
            raingMap.put("uname", uname);
            ratingMapList.add(raingMap);
        }
        return this.saveKPIZBAndDb(ratingMapList, nowMonth, companyId, tabName);
    }


    /**
     * 判断是新增还是修改
     *
     * @param mapList
     * @param nowTime
     * @param companyId
     * @throws Exception
     */
    private Integer saveKPIZBAndDb(List<Map<String, Object>> mapList, Long nowTime, String companyId, String tabName) throws Exception {
        int nums = 0;
        for (Map<String, Object> infoMap : mapList) {
            String label = StringUtil.formatNull(infoMap.get("label"));
            String count = String.valueOf(infoMap.get("count"));
            String uid = String.valueOf(infoMap.get("uid"));
            String year = DateUtils.getTimeStr(new Date(nowTime), "yyyy");
            String uname = String.valueOf(infoMap.get("uname"));
            String month = DateUtils.getTimeStr(new Date(nowTime), "MM");
            String baseYear = "";
            if(!"".equals(StringUtil.formatEmpty(infoMap.get("baseYear")))){
                baseYear=StringUtil.formatEmpty(infoMap.get("baseYear"));
            }
            Map<String, Object> kpiSgInfo = crmCarCountTaskDao.queryAccidentCarInfo(companyId, uid, year, label, tabName);
            StringBuffer sql = new StringBuffer();
            String monthStr = MONTHS[Integer.valueOf(month)];
            if (kpiSgInfo.size() == 0) {
                //新增
                sql.append("insert into " + tabName + " (id,company_id,sa,sa_id,count_tag,count_year," + monthStr + ",base_year) " +
                        "values(replace(UUID(),'-',''),'" + companyId + "','" + uname + "','" + uid + "','" + label + "','" + year + "','" + count + "','"+baseYear+"')");
            } else {
                String id = StringUtil.formatNull(kpiSgInfo.get("id"));
                //更新
                sql.append(" update " + tabName + " set " + monthStr + "='" + count + "'");
                if(!"".equals(StringUtil.formatEmpty(baseYear))){
                    sql.append(" ,base_year='"+baseYear+"' ");
                }
                sql.append(" where id='" + id + "'");
            }
            nums += this.crmCarCountTaskDao.executeSQL(sql.toString());
        }
        return nums;
    }

    /**
     * 质保回站率穿透查询
     * @param month
     * @param year
     * @param saId
     * @param uid
     * @param type
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map<String,Object> zbCarPree(String month,String year,String saId,String uid,String type,String start,String limit,String companyId) throws Exception {
        //获得月份
        month=this.getMonthIndex(month);
        if("".equals(month)){
            throw new Exception("月份选择错误!!");
        }
        String date=year+"-"+month+"-01";
        Long nowMonth = DateUtils.monthSToLongTime(date);

        //向前推35个月，由于车辆没有质保结束时间 以购买时间为准
        Long before35MonthTime = DateUtils.addMonthLongTime(nowMonth, -35);
        //向前推34个月，由于车辆没有质保结束时间 以购买时间为准
        Long before34MonthTime = DateUtils.addMonthLongTime(nowMonth, -34);

        StringBuffer sql=new StringBuffer();
        List<Object> params=new ArrayList<>();
        sql.append(" select * from ( ");
        if("car".equals(type)){
            this.zbCarPreeListToSql(uid,sql,params,before35MonthTime,before34MonthTime,companyId,saId);
        }
        if("back".equals(type)){
            this.getZbBackToFacToSql(uid,nowMonth,before35MonthTime,before34MonthTime,companyId,saId,sql,params);
        }
        sql.append(" )temp ");
        return this.crmCarCountTaskDao.page(sql.toString(), params.toArray(),start,limit);
    }

    private void zbCarPreeListToSql(String uid,StringBuffer sqlCars,List<Object> params,Long before35MonthTime,Long before34MonthTime,String companyId,String saId){
        sqlCars.append(" SELECT car.plate_num,car.vin,car.owner_name,car.owner_tel,car.brand,car.series,car.model,  car.car_age,car.run_mile   from t_crm_car car   left join  "+
                " (select detail.vin,detail.company_id,detail.u_id,cuser.u_name from t_crm_car_conf_detail detail ,t_bc_user cuser where  detail.u_id = cuser.id   and detail.company_id=? and detail.role_id='"+ Constant.SA_ROLE_ID+"'   ) detail on car.vin=detail.vin and car.company_id=detail.company_id "+
                " where car.buy_date>=? and car.buy_date<? and car.company_id=? ");
        params.add(companyId);
        params.add(before35MonthTime);
        params.add(before34MonthTime);
        params.add(companyId);
        if(!"".equals(saId)){
            if("未分配".equals(saId)){
                sqlCars.append(" and detail.u_id is null ");
            }
            else {
                sqlCars.append(" and detail.u_id=? ");
                params.add(saId);
            }
        }
        boolean isAdmin=TokenUtil.isCompanyAdmin(uid);
        if(!isAdmin){
            sqlCars.append(" and detail.u_id=? ");
            params.add(uid);
        }
        sqlCars.append(" group by car.vin ");
        if("".equals(saId)){
            sqlCars.append(" ,detail.u_id ");//分组增加人员
        }
    }

    private void getZbBackToFacToSql(String uid,Long nowMonth, Long before35MonthTime, long before34MonthTime, String companyId,String saId,
                                     StringBuffer sqlback,List<Object> params) throws Exception {
        sqlback.append("select car.plate_num,car.vin,car.owner_name,car.owner_tel,car.brand,car.series,car.model,car.car_age, max(worder.run_mile) runMile,max(worder.balance_date) balance_date from (  " +
                //增加产值大于0
                "select * from t_crm_car_workorder where real_amount > '0.00' and  wx_type in('首保','二保','定保') and company_id=?");
        params.add(companyId);
        Long beforOneMonth = DateUtils.addMonthLongTime(nowMonth, -1);
        Long nextOneMonth = DateUtils.addMonthLongTime(nowMonth, +1);
        sqlback.append("and balance_date>=? and balance_date<? ");
        params.add(beforOneMonth);
        params.add(nextOneMonth);
        sqlback.append(") worder    LEFT join (    select car.vin,detail.u_id,detail.u_name,car.plate_num,car.owner_name,car.owner_tel,car.brand,car.series,  car.model,car.car_age    from t_crm_car car   left join  "+
                " (select detail.vin,detail.company_id,detail.u_id,cuser.u_name from t_crm_car_conf_detail detail ,t_bc_user cuser where  detail.u_id = cuser.id   and detail.company_id=? and detail.role_id='"+ Constant.SA_ROLE_ID+"'   ) detail on car.vin=detail.vin and car.company_id=detail.company_id "+
                " where car.buy_date>=? and car.buy_date<? and car.company_id=? ");
        params.add(companyId);
        params.add(before35MonthTime);
        params.add(before34MonthTime);
        params.add(companyId);
        if(!"".equals(saId)){
            if("未分配".equals(saId)){
                sqlback.append(" and detail.u_id is null ");
            }
            else {
                sqlback.append(" and detail.u_id=? ");
                params.add(saId);
            }
        }
        boolean isAdmin=TokenUtil.isCompanyAdmin(uid);
        if(!isAdmin){
            sqlback.append(" and detail.u_id=? ");
            params.add(uid);
        }
        sqlback.append(")car  ");
        sqlback.append("ON    worder.vin=car.vin  where car.vin is not null  group by car.vin  ");
        if("".equals(saId)){
            sqlback.append(" ,car.u_id ");//分组增加人员
        }
    }

    //-----------------------------------定保存车辆回厂保养率-----------------------------------
    @Override
    @MethodComment(name = "定保存车辆回厂保养率")
    public Integer mainainCarBackToFacQuartz() throws Exception {
        int nums = 0;
        //1、查询出所有的公司信息
        List<Map<String, Object>> companyList = this.crmCarCountTaskDao.queryCompanyByWorkOrder("t_crm_car_workorder");
        //遍历公司信息
        for (Map<String, Object> companyMap : companyList) {
            String companyId = StringUtil.formatNull(companyMap.get("company_id"));
            //获得当前月向前推进三个月的时间 包含当前月
            //1、清除本公司下的出保kpi表
            this.crmCarCountTaskDao.executeSQL("delete from t_crm_kpi_db where company_id=?",companyId);
            Map<String,Object> map= this.getNowAndBeforThreeMonthMap(companyId, "t_crm_kpi_db");
            //插入标签及年
            saveCountYearInfo(map,companyId,"t_crm_kpi_db");
            List<String> monthList=(List<String>)map.get("times");
            //遍历月份开始计算
            for (String monthTime : monthList) {
                this.analyCompanyToDbCar(monthTime,companyId) ;
            }
        }
        return nums;
    }

    public Integer analyCompanyToDbCar(String monthTime, String companyId) throws Exception {
        Integer nums = 0;
        //当前月
        //2017年1月评测
        //2016年1月的事故车 400，
        //16年1月的事故车在16年2月1日开始至2017年2月1日之前的进厂车辆数
        //将时间格式化为月初时间
        Long nowMonth = DateUtils.monthSToLongTime(monthTime);
        //去年一月开始
        Long beforYearStartDate = DateUtils.addMonthLongTime(nowMonth, -12);
        Long beforYearSecondDate = DateUtils.addMonthLongTime(nowMonth, -11);

        Long nowNextMonthDate = DateUtils.addMonthLongTime(nowMonth, +1);

        String baseYear=DateUtils.formatLongTime(beforYearStartDate,"YYYY");
        String countYear=DateUtils.formatLongTime(nowMonth,"YYYY");
        String month = DateUtils.formatLongTime(nowMonth, "MM");
        String monthStr = MONTHS[Integer.valueOf(month)];
        /**
         * 保存车辆数
         */
        this.dbCarListsSave(beforYearStartDate,beforYearSecondDate,companyId,baseYear,countYear,monthStr);
        /**
         * 保存车辆回厂数
         */
        this.dbCarBackListSave(beforYearStartDate,beforYearSecondDate,companyId,nowNextMonthDate,monthStr,countYear);
        /**
         * 更新回店率
         */
        this.warranyCarBackRatingSave(monthStr,companyId,countYear,"t_crm_kpi_db");
        this.updBaseYear(countYear,companyId,"t_crm_kpi_db");
        return nums;
    }

    /**
     * 保存定保车辆 数
     * @param beforYearStartDate
     * @param beforYearSecondDate
     * @param companyId
     * @param baseYear
     * @param countYear
     * @param monthStr
     * @throws Exception
     */
    private void dbCarListsSave(Long beforYearStartDate, Long beforYearSecondDate, String companyId,String baseYear,
                                String countYear,String monthStr) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<Object> params=new ArrayList<>();
        sql.append("update  t_crm_kpi_db db, (" +
                " SELECT COUNT(DISTINCT worder.vin) "+monthStr+"," +
                " '车辆数' label,'"+baseYear+"' AS base_year," +
                " CASE WHEN detail.u_id IS NULL THEN '未分配'" +
                "      WHEN detail.u_id IS NOT NULL THEN detail.u_id END AS uid," +
                " CASE WHEN detail.u_name IS NULL AND detail.u_id IS NULL THEN '未分配' WHEN detail.u_name IS NOT NULL THEN detail.u_name" +
                "      WHEN detail.u_name IS NULL AND detail.u_id IS NOT NULL THEN RIGHT (detail.u_id, 8) END AS uname," +
                " worder.company_id,'"+countYear+"' count_year" +
                " FROM t_crm_car_workorder worder" +
                " LEFT JOIN (" +
                " SELECT detail.vin, detail.company_id,detail.u_id,cuser.u_name" +
                " FROM" +
                " t_crm_car_conf_detail detail,t_bc_user cuser" +
                " WHERE" +
                " detail.u_id = cuser.id AND detail.company_id =? AND detail.role_id = ? ");
        params.add(companyId);
        params.add(Constant.SA_ROLE_ID);
        sql.append(" ) detail ON worder.vin = detail.vin AND worder.company_id = detail.company_id" +
                " WHERE worder.wx_type IN ('首保', '二保', '定保') AND worder.real_amount > '0.00'" +
                " AND worder.balance_date >=? AND worder.balance_date <? AND worder.company_id =? " +
                " GROUP BY detail.u_id");
        params.add(beforYearStartDate);
        params.add(beforYearSecondDate);
        params.add(companyId);
        sql.append(" ) t" +
                " set db."+monthStr+"=t."+monthStr+",db.base_year =if(db.base_year is null ,t.base_year,db.base_year)" +
                " where db.company_id = t.company_id AND db.sa_id = t.uid AND db.sa = t.uname " +
                " AND db.count_tag = t.label AND db.count_year = t.count_year ");
        this.crmCarCountTaskDao.executeSQL(sql.toString(),params.toArray());
    }

    /**
     * 保存定保车辆回厂数
     * @param beforYearStartDate
     * @param beforYearSecondDate
     * @param companyId
     * @param nowNextMonthDate
     * @param monthStr
     * @throws Exception
     */
    private void dbCarBackListSave(Long beforYearStartDate, Long beforYearSecondDate, String companyId,
                                   Long nowNextMonthDate,String monthStr,String countYear) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<Object> params=new ArrayList<>();
        sql.append(" update t_crm_kpi_db db,(" +
                " SELECT COUNT(DISTINCT w.vin) "+monthStr+",'回店数' count_tag," +
                " CASE WHEN detail.u_id IS NULL THEN '未分配'" +
                "      WHEN detail.u_id IS NOT NULL THEN detail.u_id END AS uid," +
                " CASE WHEN detail.u_name IS NULL AND detail.u_id IS NULL THEN '未分配' WHEN detail.u_name IS NOT NULL THEN detail.u_name" +
                "      WHEN detail.u_name IS NULL AND detail.u_id IS NOT NULL THEN RIGHT (detail.u_id, 8) END AS uname," +
                " '"+countYear+"' count_year" +
                " FROM" +
                " t_crm_car_workorder w LEFT JOIN (" +
                " SELECT detail.vin, detail.company_id,detail.u_id,cuser.u_name" +
                " FROM" +
                " t_crm_car_conf_detail detail, t_bc_user cuser" +
                " WHERE" +
                " detail.u_id = cuser.id AND detail.company_id =? AND detail.role_id = ?" );
        params.add(companyId);
        params.add(Constant.SA_ROLE_ID);
        sql.append(" ) detail ON w.vin = detail.vin" +
                " AND w.company_id = detail.company_id" +
                " WHERE" +
                " w.wx_type IN ('首保', '二保', '定保') AND w.real_amount > '0.00' " +
                " AND w.balance_date >=? AND w.balance_date <? AND w.company_id =? " );
        params.add(beforYearStartDate);
        params.add(beforYearSecondDate);
        params.add(companyId);
        sql.append(" AND EXISTS (" +
                " SELECT * FROM t_crm_car_workorder" +
                " WHERE" +
                " wx_type IN ('二保', '定保') AND w.vin = vin AND balance_date >=? AND balance_date <?" +
                " AND company_id =? GROUP BY vin )");
        params.add(beforYearSecondDate);
        params.add(nowNextMonthDate);
        params.add(companyId);
        sql.append(" GROUP BY detail.u_id" +
                " )t" +
                " set db."+monthStr+"=t."+monthStr+" "+
                " where db.sa_id=t.uid and db.sa=t.uname and db.count_tag=t.count_tag " +
                " and db.count_year=t.count_year");
        this.crmCarCountTaskDao.executeSQL(sql.toString(),params.toArray());
    }

    /**
     * 定保车辆数
     *
     * @param beforYearStartDate
     * @param beforYearSecondDate
     * @param companyId
     * @return
     * @throws Exception
     */
    private void dbCarLists(Long beforYearStartDate, Long beforYearSecondDate, String companyId,String saId,
                                                 StringBuffer sql,List<Object> params) throws Exception {
        String year=DateUtils.formatLongTime(beforYearStartDate,"YYYY");
        sql.append("SELECT   COUNT(DISTINCT worder.vin) count, '车辆数' label,'"+year+"' as base_year, " +
                " CASE  WHEN detail.u_id IS NULL THEN  '未分配' WHEN detail.u_id IS NOT NULL THEN  detail.u_id  END AS uid,  " +
                " CASE  " +
                " WHEN detail.u_name is null and detail.u_id is NULL then '未分配' " +
                " WHEN detail.u_name is not null THEN detail.u_name " +
                " WHEN detail.u_name IS NULL and detail.u_id is NOT NULL THEN RIGHT(detail.u_id,8) " +
                " END AS uname  " +
                " FROM  " +
                " t_crm_car_workorder worder  " +
//                " LEFT JOIN t_crm_car_conf_detail detail ON worder.vin = detail.vin   and worder.company_id = detail.company_id " +
//                " LEFT JOIN t_bc_user cuser ON detail.u_id = cuser.id  " +
                " left join  "+
                " (select detail.vin,detail.company_id,detail.u_id,cuser.u_name from t_crm_car_conf_detail detail ,t_bc_user cuser where  detail.u_id = cuser.id " +
                " and detail.company_id=? and detail.role_id='"+ Constant.SA_ROLE_ID+"' " +
//                " and detail.u_id='8a1029015d205cfc015d30479ff761ec' " +
                " ) detail on worder.vin=detail.vin and worder.company_id=detail.company_id "+
                " WHERE  " +
                //确认产值大于0的数量
                " worder.wx_type in ('首保','二保','定保')  and worder.real_amount > '0.00'  " +
                " and worder.balance_date>=? and worder.balance_date<? and worder.company_id=?  ");
        params.add(companyId);
        params.add(beforYearStartDate);
        params.add(beforYearSecondDate);
        params.add(companyId);
        if(!"".equals(saId)){
            sql.append(" and detail.u_id=? ");
            params.add(saId);
        }
        sql.append(" group by detail.u_id");
    }

    /**
     * 定保车辆回店数
     *
     * @param beforYearStartDate
     * @param beforYearSecondDate
     * @param companyId
     * @param nowNextMonthDate
     * @return
     * @throws Exception
     */
    private void dbCarBackList(Long beforYearStartDate, Long beforYearSecondDate, String companyId,
                                                    Long nowNextMonthDate,String saId,StringBuffer sqlStr,List<Object> params) throws Exception {

        sqlStr.append("SELECT  " +
                "COUNT(DISTINCT w.vin) count,  " +
                "'回店数' label,  " +
                "CASE  WHEN detail.u_id IS NULL THEN  '未分配' WHEN detail.u_id IS NOT NULL THEN  detail.u_id  END AS uid,   " +
                "CASE  " +
                " WHEN detail.u_name is null and detail.u_id is NULL then '未分配' " +
                " WHEN detail.u_name is not null THEN detail.u_name " +
                " WHEN detail.u_name IS NULL and detail.u_id is NOT NULL THEN RIGHT(detail.u_id,8) " +
                " END AS uname    " +
                "FROM  " +
                "  t_crm_car_workorder w  " +
//                "LEFT JOIN t_crm_car_conf_detail detail ON w.vin = detail.vin   and w.company_id = detail.company_id  " +
//                "LEFT JOIN t_bc_user cuser ON detail.u_id = cuser.id    " +
                " left join  "+
                " (select detail.vin,detail.company_id,detail.u_id,cuser.u_name from t_crm_car_conf_detail detail ,t_bc_user cuser where  detail.u_id = cuser.id " +
                "  and detail.company_id=? and detail.role_id='"+ Constant.SA_ROLE_ID+"'  " +
//                " and detail.u_id='8a1029015d205cfc015d30479ff761ec' " +
                " ) detail on w.vin=detail.vin and w.company_id=detail.company_id "+
                "WHERE  " +
                //产值大于0
                "w.wx_type in('首保','二保','定保') and w.real_amount > '0.00'  and w.balance_date>=? and w.balance_date<? and w.company_id=?  ");
        params.add(companyId);
        params.add(beforYearStartDate);
        params.add(beforYearSecondDate);
        params.add(companyId);
        if(!"".equals(saId)){
            sqlStr.append(" and detail.u_id=? ");
            params.add(saId);
        }

        sqlStr.append("AND EXISTS (  " +
                "  SELECT  " +
                "    *  " +
                "  FROM  " +
                "    t_crm_car_workorder  " +
                "  WHERE  wx_type in('二保','定保')  " +
                "  AND w.vin = vin  and  balance_date>=?  and balance_date<?   and company_id=? ");
        sqlStr.append("  GROUP BY  " +
                "    vin  " +
                ")  " +
                "group by detail.u_id");
        params.add(beforYearSecondDate);
        params.add(nowNextMonthDate);
        params.add(companyId);


    }


    private String getMonthIndex(String month){
        for(int i=0;i<MONTHS.length;i++){
            String m=MONTHS[i];
            if(m.equals(month)){
                if(i<10){
                    return "0"+i;
                }else{
                    return i+"";
                }
            }
        }
        return "";
    }

    /**
     * 车辆定保回站率穿透明细
     * @param month
     * @param year
     * @param saId
     * @param uid
     * @param type
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map<String,Object> dbCarPree(String month,String year,String saId,String uid,String type,String start,String limit,String companyId) throws Exception {
        //获得月份
        month=this.getMonthIndex(month);
        if("".equals(month)){
            throw new Exception("月份选择错误!!");
        }
        String date=year+"-"+month+"-01";
        Long nowMonth = DateUtils.monthSToLongTime(date);
        //去年一月开始
        Long beforYearStartDate = DateUtils.addMonthLongTime(nowMonth, -12);
        System.out.println(DateUtils.formatLongTime(beforYearStartDate, "YYYY-MM-dd"));
        Long beforYearSecondDate = DateUtils.addMonthLongTime(nowMonth, -11);
        System.out.println(DateUtils.formatLongTime(beforYearSecondDate, "YYYY-MM-dd"));

        Long nowNextMonthDate = DateUtils.addMonthLongTime(nowMonth, +1);
        System.out.println(DateUtils.formatLongTime(nowNextMonthDate, "YYYY-MM-dd"));

        StringBuffer sql=new StringBuffer();
        List<Object> params=new ArrayList<>();
        sql.append(" select * from ( ");
        if(type.equals("car")) {
            this.dbCarPreeListToSql(uid,beforYearStartDate, beforYearSecondDate, companyId, saId, sql, params);
        }
        if(type.equals("back")) {
            this.dbCarBackPreeListToSql(uid,beforYearStartDate, beforYearSecondDate, companyId, nowNextMonthDate, saId, sql, params);
        }
        sql.append(" )temp ");
        return this.crmCarCountTaskDao.page(sql.toString(),params.toArray(),start,limit);
    }

    /**
     * 车辆数列表
     * @param beforYearStartDate
     * @param beforYearSecondDate
     * @param companyId
     * @param saId
     * @param sql
     * @param params
     * @throws Exception
     */
    private void dbCarPreeListToSql(String uid,Long beforYearStartDate, Long beforYearSecondDate, String companyId,String saId,
                                    StringBuffer sql,List<Object> params) throws Exception {
        String year=DateUtils.formatLongTime(beforYearStartDate,"YYYY");
        sql.append("SELECT car.plate_num,car.vin,car.owner_name,car.owner_tel,car.brand,car.series,car.model,  car.car_age,max(worder.run_mile) runMile,FROM_UNIXTIME(MAX(worder.balance_date) / 1000) balanceDate  FROM    t_crm_car_workorder worder    left join  "+
                " (select detail.vin,detail.company_id,detail.u_id,cuser.u_name from t_crm_car_conf_detail detail ,t_bc_user cuser where  detail.u_id = cuser.id   and detail.company_id=? and detail.role_id='"+ Constant.SA_ROLE_ID+"'    ) detail on worder.vin=detail.vin and worder.company_id=detail.company_id "+
                " LEFT JOIN t_crm_car car ON worder.vin = car.vin AND worder.company_id = car.company_id   WHERE  " +
                //确认产值大于0的数量
                " worder.wx_type in ('首保','二保','定保')  and worder.real_amount > '0.00'    and worder.balance_date>=? and worder.balance_date<? and worder.company_id=?  ");
        params.add(companyId);
        params.add(beforYearStartDate);
        params.add(beforYearSecondDate);
        params.add(companyId);
        if(!"".equals(saId)){
            if("未分配".equals(saId)){
                sql.append(" and detail.u_id is null ");
            }
            else {
                sql.append(" and detail.u_id=? ");
                params.add(saId);
            }
        }
        boolean isAdmin=TokenUtil.isCompanyAdmin(uid);
        if(!isAdmin){
            sql.append(" and detail.u_id=? ");
            params.add(uid);
        }
        sql.append(" GROUP BY worder.vin ");
        if("".equals(saId)){
            sql.append(" ,detail.u_id ");//分组增加人员
        }
    }

    /**
     * 回店数列表查询
     * @param beforYearStartDate
     * @param beforYearSecondDate
     * @param companyId
     * @param nowNextMonthDate
     * @param saId
     * @param params
     * @throws Exception
     */
    private void dbCarBackPreeListToSql(String uid,Long beforYearStartDate, Long beforYearSecondDate, String companyId,
                               Long nowNextMonthDate,String saId,StringBuffer sqlStr,List<Object> params) throws Exception {

        sqlStr.append("SELECT car.plate_num,car.vin,car.owner_name,car.owner_tel,car.brand,car.series,car.model,  car.car_age,max(w.run_mile) runMile,FROM_UNIXTIME(MAX(w.balance_date) / 1000) balanceDate  FROM    t_crm_car_workorder w    left join  "+
                " (select detail.vin,detail.company_id,detail.u_id,cuser.u_name from t_crm_car_conf_detail detail ,t_bc_user cuser where  detail.u_id = cuser.id   and detail.company_id=? and detail.role_id='"+ Constant.SA_ROLE_ID+"'    ) detail on w.vin=detail.vin and w.company_id=detail.company_id "+
                " LEFT JOIN t_crm_car car ON w.vin = car.vin AND w.company_id = car.company_id  WHERE  " +
                //产值大于0
                "w.wx_type in('首保','二保','定保') and w.real_amount > '0.00'  and w.balance_date>=? and w.balance_date<? and w.company_id=?  ");
        params.add(companyId);
        params.add(beforYearStartDate);
        params.add(beforYearSecondDate);
        params.add(companyId);
        if(!"".equals(saId)){
            if("未分配".equals(saId)){
                sqlStr.append(" and detail.u_id is null ");
            }
            else {
                sqlStr.append(" and detail.u_id=? ");
                params.add(saId);
            }
        }
        boolean isAdmin=TokenUtil.isCompanyAdmin(uid);
        if(!isAdmin){
            sqlStr.append(" and detail.u_id=? ");
            params.add(uid);
        }
        sqlStr.append("AND EXISTS (     SELECT       *     FROM       t_crm_car_workorder     WHERE  wx_type in('二保','定保')     AND w.vin = vin  and  balance_date>=?  and balance_date<?   and company_id=? ");
        params.add(beforYearSecondDate);
        params.add(nowNextMonthDate);
        params.add(companyId);
        sqlStr.append("  GROUP BY       vin   )  ");
        sqlStr.append(" GROUP BY w.vin ");
        if("".equals(saId)){
            sqlStr.append(" ,detail.u_id ");//分组增加人员
        }


    }

    public static void main(String[] args) throws Exception {
        System.out.println(DateUtils.addMonthLongTime(DateUtils.addMonthLongTime(new Date().getTime(),+1),-47));
    }
}
